/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ptr.ptrsystem.servlet;

import java.sql.*;
import com.ptr.controladores.conexionsqlserver;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author WALDIR
 */
@WebServlet(name = "BusquedaDefuncionServlet", urlPatterns = {"/BusquedaDefuncion"})
public class BusquedaDefuncionServlet extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();

        String nroActaPersonal = request.getParameter("NAP");
        String nroActaCDEF = request.getParameter("NAD");

        String ndep = request.getParameter("ndep");
        String npro = request.getParameter("npro");
        String ndis = request.getParameter("ndis");

        String nombre = request.getParameter("nombre");
        String ape1 = request.getParameter("ape1");
        String ape2 = request.getParameter("ape2");

        String BTN = request.getParameter("btn1");
        String RAM = request.getParameter("ruta");
        if (BTN.equals("buscar")) {
            try {
                String query = "select RUTA_ACTA_MAT from TB_MATRIMONIO where NRO_ACTA_MAT='" + RAM + "'";
                conexionsqlserver con = new conexionsqlserver();
                Connection cn = null;
                Statement st = null;
                ResultSet rs = null;
                con.conectar();
                cn = con.getconection();
                st = cn.createStatement();
                rs = st.executeQuery(query);
                if (rs.next()) {
                    out.println(rs.getString("RUTA_ACTA_MAT"));
                }
                cn.close();
                st.close();
                rs.close();
                con.cerrarBD();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (BTN.equals("ActaPersonal")) {
            try {
                String query = "select * from TB_DEFUNCION d,TB_PERSONA p,TB_UBIGEO ub where d.COD_PERSONA=p.COD_PERSONA and ub.idubigeo=d.IDUBIGEO and (p.COD_PERSONA LIKE('%" + nroActaPersonal + "%'))";
                conexionsqlserver con = new conexionsqlserver();
                Connection cn = null;
                Statement st = null;
                ResultSet rs = null;
                con.conectar();
                cn = con.getconection();
                st = cn.createStatement();
                rs = st.executeQuery(query);
                out.println("<center>");
                out.println("<table>");
                out.println("<thead>");
                out.println("<tr>"
                        + "<th width='20' height='30' ></th>"
                        + "<th width='150' height='30' >NRO ACT PERS</th>"
                        + "<th width='150' height='30'>NRO ACT CDEF</th>"
                        + "<th width='150' height='30'>UBIGEO</th>"
                        + "<th width='150' height='30'>NOMBRE</th>"
                        + "<th width='150' height='30'>PRIMER APELLIDO</th>"
                        + "<th width='150' height='30'>SEGUNDO APELLIDO</th>"
                        + "<th width='20' height='30' ></th>"
                        + "</tr>");
                out.println("</thead><tbody>");
                while (rs.next()) {
                    out.println("<tr><td><input type='radio' name='defuncion' value='" + rs.getString("NRO_ACTA_CDEF") + "' onclick=\"muestravalor(this)\"/></td>");
                    out.println("<td align='center'>" + rs.getString("COD_PERSONA") + "</td>");
                    out.println("<td align='center'>" + rs.getString("NRO_ACTA_CDEF") + "</td>");
                    out.println("<td align='center'>" + rs.getString("nom_ubigeo_dep") + " - " + rs.getString("nom_ubigeo_prov") + " - " + rs.getString("nom_ubigeo_dist") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRE_NOM_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRIM_APE_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("SEG_APE_PERS") + "</td>");
                    out.println("<td><a href='../Actas/" + rs.getString("RUTA_ACTA_DEF") + "'><input type='image' src='/RegCivil/imagenes/iconPdf.png' /></a></td></tr>");
                }
                out.println("<tbody></table>");
                out.println("</center>");

                cn.close();
                st.close();
                rs.close();
                con.cerrarBD();
            } catch (Exception e) {
                e.printStackTrace();
            }

            // LinkedList<Matrimonio> listaM = Lista.getListaMatrimonioXnroActaP(nroActaPersonal);
        }

        if (BTN.equals("ActaCDEF")) {
            try {
                String query = "select * from TB_DEFUNCION d,TB_PERSONA p,TB_UBIGEO ub where d.COD_PERSONA=p.COD_PERSONA and ub.idubigeo=d.IDUBIGEO and (d.NRO_ACTA_CDEF LIKE('%" + nroActaCDEF + "%'))";
                conexionsqlserver con = new conexionsqlserver();
                Connection cn = null;
                Statement st = null;
                ResultSet rs = null;
                con.conectar();
                cn = con.getconection();
                st = cn.createStatement();
                rs = st.executeQuery(query);
                out.println("<center>");
                out.println("<table>");
                out.println("<thead>");
                out.println("<tr>"
                        + "<th width='20' height='30' ></th>"
                        + "<th width='150' height='30' >NRO ACT PERS</th>"
                        + "<th width='150' height='30'>NRO ACT CDEF</th>"
                        + "<th width='150' height='30'>UBIGEO</th>"
                        + "<th width='150' height='30'>NOMBRE</th>"
                        + "<th width='150' height='30'>PRIMER APELLIDO</th>"
                        + "<th width='150' height='30'>SEGUNDO APELLIDO</th>"
                        + "<th width='20' height='30' ></th>"
                        + "</tr>");
                out.println("</thead><tbody>");
                while (rs.next()) {
                    out.println("<tr><td><input type='radio' name='defuncion' value='" + rs.getString("NRO_ACTA_CDEF") + "' onclick=\"muestravalor(this)\"/></td>");
                    out.println("<td align='center'>" + rs.getString("COD_PERSONA") + "</td>");
                    out.println("<td align='center'>" + rs.getString("NRO_ACTA_CDEF") + "</td>");
                    out.println("<td align='center'>" + rs.getString("nom_ubigeo_dep") + " - " + rs.getString("nom_ubigeo_prov") + " - " + rs.getString("nom_ubigeo_dist") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRE_NOM_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRIM_APE_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("SEG_APE_PERS") + "</td>");
                    out.println("<td><a href='../Actas/" + rs.getString("RUTA_ACTA_DEF") + "'><input type='image' src='/RegCivil/imagenes/iconPdf.png' /></a></td></tr>");
                }
                out.println("<tbody></table>");
                out.println("</center>");

                cn.close();
                st.close();
                rs.close();
                con.cerrarBD();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (BTN.equals("UbigeoD")) {
            try {
                String query = "select * from TB_DEFUNCION d,TB_PERSONA p,TB_UBIGEO ub where d.COD_PERSONA=p.COD_PERSONA and ub.idubigeo=d.IDUBIGEO and (ub.nom_ubigeo_dep like('%" + ndep.toUpperCase() + "%') and ub.nom_ubigeo_dist like('%" + ndis.toUpperCase() + "%') and ub.nom_ubigeo_prov like('%" + npro.toUpperCase() + "%'))";
                conexionsqlserver con = new conexionsqlserver();
                Connection cn = null;
                Statement st = null;
                ResultSet rs = null;
                con.conectar();
                cn = con.getconection();
                st = cn.createStatement();
                rs = st.executeQuery(query);
                out.println("<center>");
                out.println("<table>");
                out.println("<thead>");
                out.println("<tr>"
                        + "<th width='20' height='30' ></th>"
                        + "<th width='150' height='30' >NRO ACT PERS</th>"
                        + "<th width='150' height='30'>NRO ACT CDEF</th>"
                        + "<th width='150' height='30'>UBIGEO</th>"
                        + "<th width='150' height='30'>NOMBRE</th>"
                        + "<th width='150' height='30'>PRIMER APELLIDO</th>"
                        + "<th width='150' height='30'>SEGUNDO APELLIDO</th>"
                        + "<th width='20' height='30' ></th>"
                        + "</tr>");
                out.println("</thead><tbody>");
                while (rs.next()) {
                    out.println("<tr><td><input type='radio' name='defuncion' value='" + rs.getString("NRO_ACTA_CDEF") + "' onclick=\"muestravalor(this)\"/></td>");
                    out.println("<td align='center'>" + rs.getString("COD_PERSONA") + "</td>");
                    out.println("<td align='center'>" + rs.getString("NRO_ACTA_CDEF") + "</td>");
                    out.println("<td align='center'>" + rs.getString("nom_ubigeo_dep") + " - " + rs.getString("nom_ubigeo_prov") + " - " + rs.getString("nom_ubigeo_dist") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRE_NOM_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRIM_APE_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("SEG_APE_PERS") + "</td>");
                    out.println("<td><a href='../Actas/" + rs.getString("RUTA_ACTA_DEF") + "'><input type='image' src='/RegCivil/imagenes/iconPdf.png' /></a></td></tr>");
                }
                out.println("<tbody></table>");
                out.println("</center>");

                cn.close();
                st.close();
                rs.close();
                con.cerrarBD();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (BTN.equals("Nombres")) {
            try {
                String query = "select * from TB_DEFUNCION d,TB_PERSONA p,TB_UBIGEO ub where d.COD_PERSONA=p.COD_PERSONA and ub.idubigeo=d.IDUBIGEO and (p.PRE_NOM_PERS like('%" + nombre.toUpperCase() + "%') and p.PRIM_APE_PERS like('%" + ape1.toUpperCase() + "%') and p.SEG_APE_PERS like('%" + ape2.toUpperCase() + "%'))";
                conexionsqlserver con = new conexionsqlserver();
                Connection cn = null;
                Statement st = null;
                ResultSet rs = null;
                con.conectar();
                cn = con.getconection();
                st = cn.createStatement();
                rs = st.executeQuery(query);
                out.println("<center>");
                out.println("<table>");
                out.println("<thead>");
                out.println("<tr>"
                        + "<th width='20' height='30' ></th>"
                        + "<th width='150' height='30' >NRO ACT PERS</th>"
                        + "<th width='150' height='30'>NRO ACT CDEF</th>"
                        + "<th width='150' height='30'>UBIGEO</th>"
                        + "<th width='150' height='30'>NOMBRE</th>"
                        + "<th width='150' height='30'>PRIMER APELLIDO</th>"
                        + "<th width='150' height='30'>SEGUNDO APELLIDO</th>"
                        + "<th width='20' height='30' ></th>"
                        + "</tr>");
                out.println("</thead><tbody>");
                while (rs.next()) {
                    out.println("<tr><td><input type='radio' name='defuncion' value='" + rs.getString("NRO_ACTA_CDEF") + "' onclick=\"muestravalor(this)\"/></td>");
                    out.println("<td align='center'>" + rs.getString("COD_PERSONA") + "</td>");
                    out.println("<td align='center'>" + rs.getString("NRO_ACTA_CDEF") + "</td>");
                    out.println("<td align='center'>" + rs.getString("nom_ubigeo_dep") + " - " + rs.getString("nom_ubigeo_prov") + " - " + rs.getString("nom_ubigeo_dist") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRE_NOM_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("PRIM_APE_PERS") + "</td>");
                    out.println("<td align='center'>" + rs.getString("SEG_APE_PERS") + "</td>");
                    out.println("<td><a href='../Actas/" + rs.getString("RUTA_ACTA_DEF") + "'><input type='image' src='/RegCivil/imagenes/iconPdf.png' /></a></td></tr>");
                }
                out.println("<tbody></table>");
                out.println("</center>");

                cn.close();
                st.close();
                rs.close();
                con.cerrarBD();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
